package com.uyayo.oms.system.controller;

import com.uyayo.framework.common.annotation.MethodName;
import com.uyayo.framework.excelhelper.service.BaseExportExcelService;
import com.uyayo.framework.message.model.Result;
import com.uyayo.oms.system.biz.ParamBiz;
import com.uyayo.framework.pagehelper.pojo.PageBean;
import com.uyayo.framework.pagehelper.pojo.PageResult;
import com.uyayo.system.monitor.entity.SysSql;
import com.uyayo.system.monitor.model.SysSqlCondition;
import com.uyayo.system.monitor.service.SqlService;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;

import javax.servlet.http.HttpServletResponse;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;

/**
 * @title: SysSqlMonitorController
 * @description:Sql执行监控的controller
 * @company: www.j2eecms.com
 * @author 乐乐科技.
 * @date 2016-1-2下午6:03:35
 * @version 1.0
 */
@Controller
@RequestMapping("/system/monitor/sql")
public class SysSqlMonitorController {

	@Autowired
	@Qualifier("baseExportExcelXSSFService")
	private BaseExportExcelService baseExportExcelService;

	@Autowired 
	private SqlService sqlService;
	
	@Autowired
	private ParamBiz paramBiz;
	
	@RequestMapping(value="index",method=RequestMethod.GET)
	@MethodName(value="系统管理->系统监控->系统事件监控",type=1)
	public String index() throws Exception {
		return paramBiz.getManagerTemplatesPrefix()+"system/monitor/sql/index";
	}
	@ResponseBody
	@RequestMapping("grid")
	@MethodName(value="系统管理->系统监控->SQL执行监控->查询列表",type=2)
	public  PageResult<SysSql> grid(@RequestParam(value = "offset",required = false, defaultValue = "1") Integer start, //第多少行之后开始
                                    @RequestParam(value = "limit", required = false, defaultValue = "10") Integer pageSize, String sort, String order, SysSqlCondition sysSqlCondition) throws Exception {
		PageBean<SysSql> pageBean=new PageBean<>();
		try {
			pageBean = sqlService.selectSqlMonitor((start / pageSize) + 1, pageSize,sort,order,sysSqlCondition);

		} catch (Exception e) {
			e.printStackTrace();		
		}
		PageResult<SysSql> pageResult = new PageResult<>();
		pageResult.setRows(pageBean.getList());
		pageResult.setTotal(pageBean.getTotalCount());
		return pageResult;
	}
	/**
	 * 查看
	 */
	@RequestMapping(value = "view", method = RequestMethod.GET)
	@MethodName(value="系统管理->监控管理->系统事件监控->查看",type=2)
	@RequiresPermissions("system:monitor:sql:view")
	public ModelAndView show(ModelAndView modelAndView,String id) {
		SysSql sysSql = new SysSql();
		try {
			sysSql = sqlService.getSqlMonitorById(id);
		} catch (Exception e) {
			e.printStackTrace();
		}
		modelAndView.addObject("sysSql",sysSql);
		modelAndView.setViewName(paramBiz.getManagerTemplatesPrefix()+"system/monitor/sql/view");
		return modelAndView;
	}
	
	/**	
	 * 批量删除记录
	 */
	@ResponseBody
	@RequestMapping("delete")
	@RequiresPermissions("system:monitor:sql:delete")
	@MethodName(value="系统管理->系统监控->SQL执行监控->删除SQL执行监控",type=2)
	public Result deleteSqlMonitor(String ids) {
		try {
			int deleteCount = sqlService.deleteSqlMonitor(ids);
			if(deleteCount<=0){
				return Result.errorAndMsg("删除失败！");
			}
		} catch (Exception e) {
			e.printStackTrace();
			return Result.errorAndMsg("删除失败！");
		}
		return Result.successAndMsg("删除成功！");
	}
	
	
	@RequestMapping(value = "export")
	@RequiresPermissions("system:monitor:sql:export")
	@MethodName(value="系统管理->系统监控->SQL执行监控->导出Excel",type=2)
	public ModelAndView exportExcel(HSSFWorkbook workbook, HttpServletResponse response,SysSqlCondition sysSqlCondition) throws Exception {
		
		response.setContentType("octets/stream;charset=UTF-8");
		String fileName = new String("SQL执行监控".getBytes("UTF-8"),"ISO8859-1");
		response.addHeader("Content-Disposition", "attachment;filename="+fileName+".xls");
	    
	    String[] headers = {"SQL执行内容 ","执行开始时间 ","执行结束时间","SQL执行类型"};
	    String[] fieldNames = {"description","startTime","endTime","type"};

	    try {

			List<SysSql>  sqlList = sqlService.selectSqlMonitor(sysSqlCondition);
	         
	         OutputStream out = response.getOutputStream();

			this.baseExportExcelService.exportExcel(workbook,0,"系统SQL执行监控",headers,fieldNames, sqlList,"yyyy-MM-dd HH:mm:ss");

	         workbook.write(out);

	         out.close();
	         
	    } catch (FileNotFoundException e) {

	         e.printStackTrace();

	    } catch (IOException e) {

	         e.printStackTrace();

	    }
	    
		return null;
	}
	
}
